package com.hutiao.snowhb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * 
 * @author Sucb
 *
 */
public class BaseDao extends JdbcDaoSupport {
	
	private static Logger logger = Logger.getLogger(BaseDao.class);

	public int queryForInt(String sql){
		int ret = 0;
		try {
			ret = this.getJdbcTemplate().queryForInt(sql);
		} catch (IncorrectResultSizeDataAccessException e1) {
			ret = 0;
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		
		return ret;
	}

	public int queryForInt(String sql, Object[]obj){
		int ret = 0;
		try {
			ret = this.getJdbcTemplate().queryForInt(sql, obj);
		} catch (IncorrectResultSizeDataAccessException e1) {
			ret = 0;
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		
		return ret;
	}

	public Object queryForObject(String sql, Class<?> requiredType){
		
		Object ret = null;
		try {
			ret = this.getJdbcTemplate().queryForObject(sql, requiredType);
		} catch (IncorrectResultSizeDataAccessException e1) {
			ret = null;
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		
		return ret;
	}
	
	public Object queryForObject(String sql, Object[]obj, Class<?> requiredType){

		Object ret = null;
		try {
			ret = this.getJdbcTemplate().queryForObject(sql, obj, requiredType);
		} catch (IncorrectResultSizeDataAccessException e1) {
			ret = null;
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		
		return ret;
	}
	

	public Map<String, Object> queryForMap(String sql){

		List<Map<String, Object>> row = null;

		try {
			row = this.getJdbcTemplate().queryForList(sql);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		
		if (row == null || row.size() == 0) {
			return null;
		}
		
		return row.get(0);
	}
	
	
	public Map<String, Object> queryForMap(String sql, Object[]obj){
		List<Map<String, Object>> row = null;

		try {
			row = this.getJdbcTemplate().queryForList(sql, obj);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		
		if (row == null || row.size() == 0) {
			return null;
		}
		
		return row.get(0);
	}
	
	
	
	public List<Map<String, Object>> queryForList(String sql){
		List<Map<String, Object>> row = null;

		try {
			row = this.getJdbcTemplate().queryForList(sql);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		
		return row;
	}
	
	
	public List<Map<String, Object>> queryForList(String sql, Object[]obj){
		List<Map<String, Object>> row = null;

		try {
			row = this.getJdbcTemplate().queryForList(sql, obj);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		
		return row;
	}
	
	public int update(String sql){
		int ret = 0;
		try {
			ret = this.getJdbcTemplate().update(sql);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		return ret;
	}
	
	public int update(String sql, Object[]obj){
		int ret = 0;
		try {
			ret = this.getJdbcTemplate().update(sql, obj);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();			
			logger.error(e2.getMessage());
			logger.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:" + sqle.getSQLState());
			throw e2;
		}
		return ret;
	}
	
	public void execute(String sql){
		this.getJdbcTemplate().execute(sql);
	}
	
	
	
	
	public Integer save(String sql) throws SQLException {
		
		Integer id = null;
		Connection conn = getJdbcTemplate().getDataSource().getConnection();
		PreparedStatement state = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        
        state.executeUpdate();
        ResultSet rs = state.getGeneratedKeys();
        if (rs.next()) {
        	id = rs.getInt(1);
        }
        
        return id;
		
	}
	
	
	
	@SuppressWarnings("unchecked")
	public boolean executeBatch(List sql) {
		Connection conn = null;
		Statement stat = null;
		try {
			conn = getJdbcTemplate().getDataSource().getConnection();
			stat = conn.createStatement();
			for (int i = 0; i < sql.size(); i++) {
				stat.addBatch(sql.get(i).toString());
			}
			stat.executeBatch();
			return (true);
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
			return (false);
		} finally {
			try {
				if(stat != null) {
					stat.close();
				}
				if(conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e.getMessage());
				e.printStackTrace();
			}
			
		}
	
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> find(String sql, Object[] args) {
		List<Map<String, Object>> rows;
		try {
			rows = this.getJdbcTemplate().queryForList(sql, args);
		} catch (DataAccessException e) {
			logger.error("数据库操作异常，错误信息："+e.getMessage());
			e.printStackTrace();
			return null;
		} finally {
			try {
				this.getJdbcTemplate().getDataSource().getConnection().close();
			} catch (SQLException e) {
				logger.error("数据库操作异常，错误信息："+e.getMessage());
				e.printStackTrace();
			}
		}

		Iterator it = rows.iterator();
		if (it.hasNext()) {
			return rows;
		}

		return null;
	}
	
}
